﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using FreeOnlineApplication.Class.Entity;
using MySql.Data.MySqlClient;
using System.Configuration;
using System.Collections;

namespace FreeOnlineApplication.Class.Repository
{
    public class FacilityRepository
    {
        protected MySqlConnection m_connection;
        MySqlCommand aCommand = new MySqlCommand();

        public FacilityRepository(MySqlConnection connection)
        {
            m_connection = connection;
            aCommand.Connection = m_connection;
        }
        public void Insert(Facility facility)
        {
            string insertSQL = String.Format(@"insert into table_facility 
                (
                    facility_code,
                    facility_name,
                    facility_serviceprice,
                    facility_stock,
                    unit_id) 
                VALUES ('{0}','{1}',{2},{3},{4})",
                facility.CODE.Trim(), facility.NAME, facility.SERVICE_PRICE, facility.STOCK, facility.UNIT.ID);
            aCommand.CommandText = insertSQL;
            aCommand.ExecuteNonQuery();
            facility.ID = GetMaximumId();
        }
        public Facility GetById(int id)
        {
            string getByID = String.Format("select * from table_facility where facility_id = {0}", id);
            aCommand.CommandText = getByID;
            MySqlDataReader aReader = aCommand.ExecuteReader();
            Facility facility = transform(aReader);
            aReader.Close();
            return facility;
        }
        public void Update(Facility facility)
        {
            string updateSQL = String.Format(@"update table_facility set 
                    facility_code = '{0}',
                    facility_name = '{1}',
                    facility_serviceprice = {2},
                    facility_stock = {3},
                    unit_id = {4}
                where facility_id = {5}",
                facility.CODE.Trim(), facility.NAME, facility.SERVICE_PRICE, facility.STOCK, facility.UNIT.ID, facility.ID);
            aCommand.CommandText = updateSQL;
            aCommand.ExecuteNonQuery();
        }
        public void Delete(Facility facility)
        {
            string deleteSQL = "delete from table_facility where facility_id = " + facility.ID;
            aCommand.CommandText = deleteSQL;
            aCommand.ExecuteNonQuery();
        }
        public int GetMaximumId()
        {
            string getByID = String.Format("SELECT max(facility_id) from table_facility");
            aCommand.CommandText = getByID;
            object r = aCommand.ExecuteScalar();
            int aReader = r is System.DBNull ? 0 : Convert.ToInt32(r);
            return aReader;
        }
        public int GetCount(int unit)
        {
            string getByID = String.Format("SELECT count(*) from table_facility where unit_id = {0}", unit);
            aCommand.CommandText = getByID;
            object r = aCommand.ExecuteScalar();
            int aReader = r is System.DBNull ? 0 : Convert.ToInt32(r);
            return aReader;
        }
        public IList GetAll(int facility)
        {
            string sql = String.Format("select * from table_facility where unit_id = {0}", facility);
            aCommand.CommandText = sql;
            MySqlDataReader aReader = aCommand.ExecuteReader();
            IList users = transformList(aReader);
            aReader.Close();
            return users;
        }
        public IList GetAllCodes(int unitID)
        {
            string sql = String.Format("select * from table_facility where unit_id = {0}", unitID);
            aCommand.CommandText = sql;
            MySqlDataReader aReader = aCommand.ExecuteReader();
            IList result = new ArrayList();
            while (aReader.Read())
            {
                result.Add(aReader["facility_code"].ToString() + " : " + aReader["facility_name"].ToString());
            }
            aReader.Close();
            return result;
        }
        public Facility GetByCode(string code, int unitID)
        {
            string sql = String.Format("select * from table_facility where facility_code = '{1}' and unit_id = {0}", unitID, code);
            aCommand.CommandText = sql;
            MySqlDataReader aReader = aCommand.ExecuteReader();
            Facility fac = transform(aReader);
            aReader.Close();
            return fac;
        }
        private Facility transform(MySqlDataReader aReader)
        {
            Facility facility = null;
            while (aReader.Read())
            {
                facility = new Facility();
                facility.ID = Convert.ToInt32(aReader["facility_id"]);
                facility.CODE = aReader["facility_code"].ToString();
                facility.NAME = aReader["facility_name"].ToString();
                facility.UNIT = new Unit(Convert.ToInt32(aReader["unit_id"]));
                facility.SERVICE_PRICE = Convert.ToDouble(aReader["facility_serviceprice"]);
                facility.STOCK = Convert.ToDouble(aReader["facility_stock"]);
            }
            return facility;
        }
        private IList transformList(MySqlDataReader aReader)
        {
            IList result = new ArrayList();
            while (aReader.Read())
            {
                Facility facility = new Facility();
                facility.ID = Convert.ToInt32(aReader["facility_id"]);
                facility.CODE = aReader["facility_code"].ToString();
                facility.NAME = aReader["facility_name"].ToString();
                facility.UNIT = new Unit(Convert.ToInt32(aReader["unit_id"]));
                facility.SERVICE_PRICE = Convert.ToDouble(aReader["facility_serviceprice"]);
                facility.STOCK = Convert.ToDouble(aReader["facility_stock"]);
                result.Add(facility);
            }
            return result;
        }
    }
}
